from django.shortcuts import render_to_response
from django.template import RequestContext
from django.db.models import Count, Sum, Avg

from browsertests.tests.models import Test
from browsertests.specs.models import Spec
from browsertests.results.models import Result
from browsertests.useragents.models import Useragent

def home(request):
    test_count = Test.objects.count()
    result_count = Result.objects.count()

    # raw queries:
    #from django.db import connection
    #cursor = connection.cursor()
    #cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
    #row = cursor.fetchone()

    # XXX Should use select_related() here, but there are some issues.
    # See http://code.djangoproject.com/ticket/10127
    #uas = Useragent.objects.select_related().annotate(
    uas = Useragent.objects.annotate(
            Count('result'), Sum('result__succeeded'),
            Avg('result__duration'), Sum('result__duration'))
    for ua in uas:
        if ua.result__count is None:
            ua.result__count = 0
        if ua.result__succeeded__sum is None:
            ua.result__succeeded__sum = 0
        # convert to hours
        if ua.result__duration__sum:
            ua.result__duration__sum /= 3600.
        ua.coverage = 0
        if test_count:
            ua.coverage = (float(ua.result__count) / test_count) * 100.0
        ua.success_rate = 0
        if ua.result__count:
            ua.success_rate = (float(ua.result__succeeded__sum) /
                               ua.result__count) * 100.0

    # XXX this counts multiple times a test that belongs to several sections in
    # the same spec.
    specs = Spec.objects.extra(select={
        'sum_tests':
            'SELECT COUNT(*) FROM tests_test '
            'JOIN tests_test_sections ON tests_test.id = tests_test_sections.test_id '
            'JOIN specs_section ON tests_test_sections.section_id = specs_section.id '
            'WHERE specs_section.spec_id = specs_spec.id',
        'sum_sections':
            'SELECT COUNT(*) FROM specs_section '
            'WHERE specs_section.spec_id = specs_spec.id',
    })

    return render_to_response('home.html', {
        'test_count': test_count,
        'result_count': result_count,
        'uas': uas,
        'specs': specs,
    },
    context_instance=RequestContext(request))
